package com.xqgl.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.xqgl.bean.Complaint;
import com.xqgl.dao.ComplaintDao;
import com.xqgl.util.ConnertionUtil;

public class ComplaintDaoImp implements ComplaintDao{
	Connection conn = ConnertionUtil.getConnection();
	PreparedStatement ps;
	Statement statement;
	
	public ArrayList<Complaint> getAllUserInfos() {
		ArrayList<Complaint> complaints = new ArrayList<>();
		String sql = "select * from complaint ";
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rst=ps.executeQuery(sql);
			rst.last();
			rst.beforeFirst();
			while(rst.next()) {
				Complaint complaint = new Complaint();
				complaint.setId(rst.getInt("id"));
				complaint.setVillagename(rst.getString("villagename"));
				complaint.setComplainants(rst.getString("complainants"));
				complaint.setComplaintname(rst.getString("complaintname"));
				complaint.setComplaintrea(rst.getString("complaintrea"));
				complaint.setComplaintctime(rst.getString("complaintctime"));
				complaint.setComplaintstate(rst.getString("complaintstate"));
				complaints.add(complaint);
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return complaints;
	}
	
	@Override
	public int add(Complaint complaint) throws SQLException {
		String sql = "insert into complaint(villagename,complainants,complaintname,complaintrea,complaintctime,complaintstate)"
				+ "values(?,?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, complaint.getVillagename());
		ps.setString(2, complaint.getComplainants());
		ps.setString(3, complaint.getComplaintname());
		ps.setString(4, complaint.getComplaintrea());
		ps.setString(5, complaint.getComplaintctime());
		ps.setString(6, complaint.getComplaintstate());
		int a = ps.executeUpdate();
		ps.close();
		return a;
	}
	@Override
	public int update(Complaint complaint) throws SQLException {
		String sql = "update complaint set villagename = '"+complaint.getVillagename()+"', complainants = '"+complaint.getComplainants()+"', complaintname = '"+complaint.getComplaintname()+"',"
				+ "complaintrea = '"+complaint.getComplaintrea()+"', complaintctime = '"+complaint.getComplaintctime()+"', complaintstate = '"+complaint.getComplaintstate()+"' where id = "+complaint.getId()+"";
		ps = conn.prepareStatement(sql);
		int b = ps.executeUpdate();
		ps.close();
		return b;
	}
	@Override
	public boolean delete(int id) throws SQLException {
		String sql = "delete from complaint where id = "+id+"";
		statement = conn.createStatement();
		boolean c = statement.execute(sql);
		statement.close();
		return !c;
	}
	@Override
	public Complaint findById(int id) throws SQLException {
		Complaint complaint = new Complaint();
		String sql = "select * from complaint where id = ?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet set = ps.executeQuery();
		while(set.next()){
			complaint.setId(set.getInt("id"));
			complaint.setVillagename(set.getString("villagename"));
			complaint.setComplainants(set.getString("complainants"));
			complaint.setComplaintname(set.getString("complaintname"));
			complaint.setComplaintrea(set.getString("complaintrea"));
//			complaint.setComplaintctime(set.getString("complaintctime"));
			complaint.setComplaintstate(set.getString("complaintstate"));
		}
		ps.close();
		return complaint;
	}
}
